const Business = require('../entity/Business')
const Man = require('../entity/Man')
const EmployeeAttendance = require('../entity/EmployeeAttendance')
const sequelize = require("../init/sequelize");

async function getBusinessData(req) {
    const {year, month} = req.query
    const businessData = await Business.findAll({
        where: {
            year: year,
            month: month
        },
        attributes: ['day', 'averPrice', 'turnover']
    });
    const days = [];
    const averPrices = [];
    const turnovers = [];
    businessData.forEach(data => {
        days.push(data.day);
        averPrices.push(data.averPrice);
        turnovers.push(data.turnover);
    });
    return {days, averPrices, turnovers}
}

async function findPage(req) {
    const {pageNum, pageSize, searchDay} = req.query;
    const pageNumInt = parseInt(pageNum);
    const pageSizeInt = parseInt(pageSize);
    if (searchDay != 0) {
        const offset = (pageNumInt - 1) * pageSizeInt; // 计算偏移量
        const absentEmployees = await Man.findAndCountAll({
            attributes: [
                [sequelize.col('Man.ID'), '员工id'],
                [sequelize.col('Man.name'), '员工name'],
                [sequelize.col('Man.work'), '员工work'],
                [sequelize.fn('COUNT', sequelize.literal('*')), '员工缺勤天数']
            ],
            include: [{
                model: EmployeeAttendance,
                attributes: [],
                where: {
                    isAttendance: 0
                }
            }],
            group: ['Man.ID', 'Man.name', 'Man.work'],
            having: sequelize.literal('COUNT(*) <= :searchDay'),
            replacements: {searchDay: searchDay},
            limit: pageSizeInt,
            offset: offset
        });
        return absentEmployees.count;
    } else {
        return new Promise((resolve, reject) => {
            const absentCountZeroData = [];
            EmployeeAttendance.findAll({
                attributes: [
                    'id',
                    [sequelize.fn('COUNT', sequelize.literal('CASE WHEN isAttendance = 0 THEN 1 END')), 'absentCount']
                ],
                group: ['id']
            })
                .then(async (results) => {
                    // 对每个 id 查询对应的 name 和 work
                    for (const result of results) {
                        const id = result.getDataValue('id');
                        const absentCount = result.getDataValue('absentCount');
                        const employee = await Man.findOne({
                            attributes: ['name', 'work'],
                            where: {ID: id}
                        });
                        const name = employee.getDataValue('name');
                        const work = employee.getDataValue('work');

                        if (absentCount === 0) {
                            absentCountZeroData.push({ID: id, name, work, count: absentCount});
                        }
                    }
                    resolve(absentCountZeroData); // 在所有操作执行完毕后返回结果
                })
                .catch((error) => {
                    reject(error);
                });
        });
    }
}

async function getTodayData(req) {
    const [yearStr, monthStr, dayStr] = req.query.todayTime.split('-');
    const year = parseInt(yearStr);
    const month = parseInt(monthStr);
    const day = parseInt(dayStr);
    const businessData = await Business.findOne({
        where: {
            year: year,
            month: month,
            day: day
        },
        attributes: ['averPrice', 'turnover']
    });
    const averPrice = businessData.averPrice;
    const turnover = businessData.turnover;
    return {averPrice, turnover}
}

async function getDataByYear(req) {
    const [yearStr, monthStr, dayStr] = req.query.todayTime.split('-');
    const year = parseInt(yearStr);
// 查询数据库获取指定年份的数据
    const businessData = await Business.findAll({
        attributes: ['month', 'turnover', 'averPrice'],
        where: {
            year: year
        }
    });

    // 初始化月份标签和数据数组
    const monthsLabels = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'];
    const turnoverData = Array.from({length: 12}, () => 0);
    const averPriceData = Array.from({length: 12}, () => 0);

    // 计算每个月的营业额总和和数量
    const monthTotalTurnover = Array.from({length: 12}, () => 0);
    const monthCountTurnover = Array.from({length: 12}, () => 0);
    const monthTotalAverPrice = Array.from({length: 12}, () => 0);
    const monthCountAverPrice = Array.from({length: 12}, () => 0);

    businessData.forEach(item => {
        const month = item.month - 1; // 月份从0开始，需要减1
        monthTotalTurnover[month] += item.turnover;
        monthCountTurnover[month]++;
        monthTotalAverPrice[month] += item.averPrice;
        monthCountAverPrice[month]++;
    });

    // 计算每个月的平均营业额
    for (let i = 0; i < 12; i++) {
        if (monthCountTurnover[i] > 0) {
            turnoverData[i] = Math.round(monthTotalTurnover[i] / monthCountTurnover[i]);
        }
    }
    for (let i = 0; i < 12; i++) {
        if (monthCountAverPrice[i] > 0) {
            averPriceData[i] = Math.round(monthTotalAverPrice[i] / monthCountAverPrice[i]);
        }
    }

    return {monthsLabels, turnoverData, averPriceData};
}

async function getEmployeeAttendanceData(req) {
    const [yearStr, monthStr, dayStr] = req.query.todayTime.split('-');
    const year = parseInt(yearStr);
    const month = parseInt(monthStr);
    // 执行带有参数绑定的原生SQL查询
    const [results, metadata] = await sequelize.query(`
        SELECT e.ID,
               e.name,
               e.work,
               e.phone,
               COALESCE(SUM(CASE WHEN a.isAttendance = 0 THEN 1 ELSE 0 END), 0) AS total_attendance,
               GROUP_CONCAT(CASE WHEN a.isAttendance = 0 THEN a.day END)        AS attendance_days
        FROM employees AS e
                 LEFT JOIN
             employeeattendance AS a ON e.ID = a.employee_id
        WHERE a.year = :year
          AND a.month = :month
        GROUP BY e.ID, e.name, e.work, e.phone
        ORDER BY total_attendance DESC;
    `, {
        replacements: {year, month}
    });
    return {results};
}

async function getOption(req) {
    const [result, metadata1] = await sequelize.query(`
        select year
        from business
        GROUP BY year
    `);
    const options1 = result.map(option => ({
        value: option.year,
        label: `${option.year}`
    }));
    const options2 = [
        { value: 1, label: '一月' },
        { value: 2, label: '二月' },
        { value: 3, label: '三月' },
        { value: 4, label: '四月' },
        { value: 5, label: '五月' },
        { value: 6, label: '六月' },
        { value: 7, label: '七月' },
        { value: 8, label: '八月' },
        { value: 9, label: '九月' },
        { value: 10, label: '十月' },
        { value: 11, label: '十一月' },
        { value: 12, label: '十二月' }
    ];
    return {options1,options2};
}

module.exports = {
    getBusinessData, findPage, getTodayData, getDataByYear, getEmployeeAttendanceData, getOption
};